package com.ycw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ycw.dao.TenantDao;
//�⻧����ɾ�Ĳ�
import com.ycw.util.DBUtil;

//租户的增删改查
public class TenantDaoImpl implements TenantDao {
	private Connection conn;
	private PreparedStatement ps;
	private Statement stmt;
	private ResultSet res;
	DBUtil util = new DBUtil();
	int i = 10;

	// 返回总页码
	public int tenantGetPage() throws Exception {
		int t_idPages = 0;
		conn = this.util.getConn();
		String sql = "select count(t_id) from tenant";
		ps = conn.prepareStatement(sql);
		res = ps.executeQuery();
		while (res.next()) {
			t_idPages = res.getInt("count(t_id)");
		}

		return t_idPages;
	}

	// 所有租户的显示 分页
	@Override
	public Map<String, Object> tenantGetAll(int page) {
		Map<String, Object> resMap = new HashMap<>();
		try {
			int t_idPages = tenantGetPage(); // 数据总数
			conn = this.util.getConn();
			int begin = (page - 1) * i;
			String sql = "select * from tenant limit ?,?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, begin);
			ps.setInt(2, i);
			res = ps.executeQuery();
			// 三元运算，页数
			int pages = t_idPages % i == 0 ? t_idPages / i : t_idPages / i + 1;
			while (res.next()) {
				Map<String, Object> map = new HashMap<>();
				map.put("t_id", res.getInt("t_id"));
				map.put("t_name", res.getString("t_name"));
				map.put("t_phone", res.getString("t_phone"));
				map.put("t_sex", res.getString("t_sex"));
				map.put("t_native", res.getString("t_native"));
				map.put("t_number", res.getString("t_number"));
				map.put("t_time", res.getString("t_time"));
				map.put("pages", pages);
				map.put("t_idPages", t_idPages);
				Integer i = res.getInt("t_id");
				resMap.put(i.toString(), map);
			}
			;
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return resMap;
	}

	// 根据租户表编号删除指定列 包括中间表
	@Override
	public boolean tenantDeleteById(int t_id) {
		// TODO Auto-generated method stub
		int h_id = 0;
		boolean f = false;
		try {
			// 找到house状态修改
			conn = this.util.getConn();
			String sql = "select h_id from rent where t_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t_id);
			res = ps.executeQuery();
			while (res.next()) {
				h_id = res.getInt("h_id");
			}
			// 修改
			sql = "update house set h_state=0 where h_id=? ";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, h_id);
			int n = ps.executeUpdate();
			if (n > 0) {
				// 删除租户表
				sql = " delete from tenant where t_id=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, t_id);
				int j = ps.executeUpdate();
				if (j > 0) {
					// 删除中间表
					sql = "delete from rent where t_id=?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, t_id);
					// System.out.println(sql);
					int m = ps.executeUpdate();
					if (m > 0) {
						f = true;
					}
				}
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return f;

	}

	// 修改租户信息
	@Override
	public boolean tenantUpdateByStringServlet(String[] updateOne, int t_id, String[] updateAddress) {
		// TODO Auto-generated method stub
		boolean f = false;
		int h_id = 0;
		// 调用详情页方法
		tenantDetailById(t_id);
		try {
			conn = this.util.getConn();
			String sql = " update tenant set t_name=?,t_phone=?,t_sex=?,t_native=?,t_number=?,t_time=? where t_id=?";
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < updateOne.length; i++) {
				ps.setString(i + 1, updateOne[i]);
			}
			ps.setInt(7, t_id);
			int j = ps.executeUpdate();
			if (j > 0) {
				// 查询h_id
				sql = "select h_id from house where h_region =? and h_village=? and h_unit=? and h_roomnum=?";
				ps = conn.prepareStatement(sql);
				for (int i = 0; i < updateAddress.length; i++) {
					ps.setString(i + 1, updateAddress[i]);
				}
				res = ps.executeQuery();
				while (res.next()) {
					h_id = res.getInt("h_id");
				}
				// 更新rent表 中间表
				sql = "update rent set h_id=? where t_id=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, h_id);
				ps.setInt(2, t_id);
				int m = ps.executeUpdate();
				if (m > 0) {
					f = true;
				}
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return f;
	}

	// 租户详情页 显示用户所有信息和房子地址
	@Override
	public Map<String, Object> tenantDetailById(int t_id) {
		Map<String, Object> resMap = new HashMap<>();
		try {
			conn = this.util.getConn();
			String sql = "SELECT h.h_id,h.h_region,h.h_village,h.h_unit,h.h_roomnum ,t.* from tenant t,rent r,house h where t.t_id=r.t_id and r.h_id=h.h_id and t.t_id=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, t_id);
			res = ps.executeQuery();
			while (res.next()) {
				Map<String, Object> map = new HashMap<>();
				map.put("t_id", res.getInt("t_id"));
				map.put("t_name", res.getString("t_name"));
				map.put("t_phone", res.getString("t_phone"));
				map.put("t_sex", res.getString("t_sex"));
				map.put("t_native", res.getString("t_native"));
				map.put("t_number", res.getString("t_number"));
				map.put("t_time", res.getString("t_time"));
				map.put("h_id", res.getInt("h_id"));
				map.put("h_region", res.getString("h_region"));
				map.put("h_village", res.getString("h_village"));
				map.put("h_unit", res.getString("h_unit"));
				map.put("h_roomnum", res.getString("h_roomnum"));
				Integer i = res.getInt("t_id");
				resMap.put(i.toString(), map);
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return resMap;
	}

	// 查询返回总数据
	public int tenantGetCheckPage(String t_name, String t_phone, String t_time, int page) throws Exception {
		conn = this.util.getConn();
		List list = new ArrayList();
		// 查询的所有数据
		int checkCount = 0;
		StringBuffer sql = new StringBuffer("select count(t_id) from tenant where 1=1 ");
		if (t_name != "") {
			sql.append(" and t_name=?");
			list.add(t_name);
		}
		if (t_phone != "") {
			sql.append(" and t_phone=?");
			list.add(t_phone);
		}
		if (t_time != "") {
			sql.append(" and t_time=?");
			list.add(t_time);
		}
		String sql1 = sql.toString();
		ps = conn.prepareStatement(sql1);
		for (int i = 0; i < list.size(); i++) {
			ps.setObject(i + 1, list.get(i));
		}
		res = ps.executeQuery();
		while (res.next()) {
			checkCount = res.getInt("count(t_id)");
		}
		return checkCount;
	}

	// 租户按租户姓名、手机号、入住时间查询
	@Override
	public Map<String, Object> tenantCheckByName(String t_name, String t_phone, String t_time, int page) {
		Map<String, Object> resMap = new HashMap<>();
		try {
			// 接受总数据
			int checkCount = tenantGetCheckPage(t_name, t_phone, t_time, page);
			// 总页码
			int pages = checkCount % i == 0 ? checkCount / i : checkCount / i + 1;
			conn = this.util.getConn();
			List list = new ArrayList();
			StringBuffer sql = new StringBuffer("select * from tenant where 1=1 ");
			if (t_name != "") {
				sql.append(" and t_name=?");
				list.add(t_name);
			}
			if (t_phone != "") {
				sql.append(" and t_phone=?");
				list.add(t_phone);
			}
			if (t_time != "") {
				sql.append(" and t_time=?");
				list.add(t_time);
			}
			sql.append(" limit ?,10");// 追加分页条件
			// list.add(arg0);
			int begin = (page - 1) * i;
			list.add(begin);
			String sql1 = sql.toString();
			ps = conn.prepareStatement(sql1);
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i + 1, list.get(i));
				// System.out.println(list.size());
			}
			// ps.setInt(list.size()+1, begin);

			res = ps.executeQuery();
			while (res.next()) {
				Map<String, Object> map = new HashMap<>();
				map.put("t_name", res.getString("t_name"));
				map.put("t_number", res.getString("t_number"));
				map.put("t_time", res.getString("t_time"));
				map.put("t_id", res.getInt("t_id"));
				map.put("t_sex", res.getString("t_sex"));
				map.put("t_native", res.getString("t_native"));
				map.put("t_phone", res.getString("t_phone"));
				map.put("checkCount", checkCount);
				map.put("pages", pages);
				int i = res.getInt("t_id");
				String t_id = Integer.toString(i);
				resMap.put(t_id, map);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}

		return resMap;
	}

	// 添加一条新的租户
	@Override
	public boolean tenantAddByString(String[] getAllTenant, int h_id) {
		boolean f = false;
		int t_id = 0;
		try {
			conn = this.util.getConn();
			String sql = " insert into tenant(t_name,t_phone,t_sex,t_native,t_number,t_time) values(?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < getAllTenant.length; i++) {
				ps.setString(i + 1, getAllTenant[i]);
			}
			String t_number = getAllTenant[4];
			int j = ps.executeUpdate();
			if (j > 0) {
				sql = " select t_id from tenant where t_number=? ";
				ps = conn.prepareStatement(sql);
				ps.setString(1, t_number);
				res = ps.executeQuery();
				while (res.next()) {
					t_id = res.getInt("t_id");
				}
				// 修改house表 入住状态
				sql = "update house set h_state=1 where h_id=?";
				ps = conn.prepareStatement(sql);
				ps.setInt(1, h_id);
				int n = ps.executeUpdate();
				if (n > 0) {
					// 插入rent表
					sql = "insert into rent (h_id,t_id) VALUES(?,?) ";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, h_id);
					ps.setInt(2, t_id);
					int m = ps.executeUpdate();
					if (m > 0) {
						f = true;
					}
				}
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return f;
	}

	// 找到h_id
	@Override
	public int tenantAddByAddressGetId(String h_region, String h_village, String h_unit, String h_roomnum) {
		int h_id = 0;
		// 查询h_id
		try {
			conn = this.util.getConn();
			String sql = "select h_id from house where h_region =? and h_village=? and h_unit=? and h_roomnum=? and h_state=0";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			ps.setString(2, h_village);
			ps.setString(3, h_unit);
			ps.setString(4, h_roomnum);
			res = ps.executeQuery();
			while (res.next()) {
				h_id = res.getInt("h_id");
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return h_id;
	}

	/**
	 * 查询地区 结果返回 所有状态为0的地区
	 */
	@Override
	public List<String> tenantRegion() {
		List<String> list = new ArrayList();
		try {
			conn = this.util.getConn();
			String sql = " select DISTINCT h_region from house where h_state=0";
			ps = conn.prepareStatement(sql);
			// ps.setString(1, h_region);
			res = ps.executeQuery();
			while (res.next()) {
				list.add(res.getString("h_region"));
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 查询地区,小区 结果返回 所有状态为0的小区
	 */
	@Override
	public List<String> tenantVillage(String h_region) {
		List<String> list = new ArrayList();
		try {
			conn = this.util.getConn();
			String sql = " select DISTINCT h_village from house where h_region=? and h_state=0";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			res = ps.executeQuery();
			while (res.next()) {
				list.add(res.getString("h_village"));
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	/**
	 * 查询地区，小区，单元号 结果返回 所有状态为0的单元号
	 */
	@Override
	public List<String> tenantUnit(String h_region, String h_village) {
		List<String> list = new ArrayList();
		try {
			conn = this.util.getConn();
			String sql = " select DISTINCT h_unit from house where h_region=? and h_village =? and h_state=0";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			ps.setString(2, h_village);
			res = ps.executeQuery();
			while (res.next()) {
				list.add(res.getString("h_unit"));
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	/**
	 * 查询地区 ,小区，单元号，房间号 结果返回 所有状态为0的房间号
	 */
	@Override
	public List<String> tenantRoomnum(String h_region, String h_village, String h_unit) {
		List<String> list = new ArrayList();
		try {
			conn = this.util.getConn();
			String sql = " select DISTINCT h_roomnum from house where h_region=? and h_village =? and h_unit=? and h_state=0;";
			ps = conn.prepareStatement(sql);
			ps.setString(1, h_region);
			ps.setString(2, h_village);
			ps.setString(3, h_unit);
			res = ps.executeQuery();
			while (res.next()) {
				list.add(res.getString("h_roomnum"));
			}
			this.util.closeConn();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;

	}

	@Override
	public void tenantAdd() {
		// TODO Auto-generated method stub

	}

	@Override
	public void tenantDelete() {
		// TODO Auto-generated method stub

	}

	@Override
	public void tenantUpdate() {
		// TODO Auto-generated method stub

	}

	@Override
	public void tenantCheck() {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean tenantAddByString(String[] getAllTenant, String[] getAllHouse) {
		// TODO Auto-generated method stub
		return false;
	}
}
